--HEADER Improve EconomicSector table structure
--SKIPTRANSACTION

-- Adds new table for EconomicsSectors, import data from previous table and deletes previous table
CREATE TABLE [MarketData].[EconomicSectorsStructure]
(
	Id INT IDENTITY PRIMARY KEY,
	[SectorId] INT,
	[ParentId] INT FOREIGN KEY REFERENCES MarketData.EconomicSectorsStructure(Id),
	[Description] VARCHAR(100) NOT NULL
);

SET IDENTITY_INSERT [MarketData].[EconomicSectorsStructure] ON
INSERT INTO [MarketData].[EconomicSectorsStructure]([Id], [SectorId], [ParentId], [Description]) VALUES(0, 0, NULL, 'Unclassified')
SET IDENTITY_INSERT [MarketData].[EconomicSectorsStructure] OFF
GO

DECLARE @id INT
DECLARE @economicsId INT
DECLARE @businessId INT
DECLARE @industryId INT
DECLARE @description VARCHAR(100)

------------------------------------------------------------------------
------------- Reads level 3 nodes (industry sectors) first -------------
------------- to preserve existing economic sectors ids    -------------
------------------------------------------------------------------------

DECLARE industryCursor CURSOR FOR
SELECT [Id], [IndustryGroupId], [IndustryGroupDescription]
FROM [MarketData].[EconomicSector] WHERE [IndustryGroupId] <> 0 OR ([IndustryGroupDescription] <> '' AND [IndustryGroupDescription] <> 'Unclassified' AND [IndustryGroupDescription] <> 'Unknown');
OPEN industryCursor
FETCH NEXT FROM industryCursor INTO @id, @industryId, @description

-- Insert with existing IDs in order not to brake existing foreign keys
SET IDENTITY_INSERT [MarketData].[EconomicSectorsStructure] ON

WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO [MarketData].[EconomicSectorsStructure]([Id], [SectorId], [Description]) 
	VALUES (@id, @industryId, @description);
	
	FETCH NEXT FROM industryCursor INTO @id, @industryId, @description
END

CLOSE industryCursor
DEALLOCATE industryCursor

SET IDENTITY_INSERT [MarketData].[EconomicSectorsStructure] OFF

------------------------------------------------------------------
------------- Reads level 2 nodes (business sectors) -------------
------------------------------------------------------------------

DECLARE @parentId INT
DECLARE @childDescription VARCHAR(100)

DECLARE businessCursor CURSOR FOR
SELECT [BusinesSectorId], [BusinesSectorDescription], [IndustryGroupId], [IndustryGroupDescription]
FROM [MarketData].[EconomicSector] WHERE [BusinesSectorId] <> 0 OR ([BusinesSectorDescription] <> '' AND [BusinesSectorDescription] <> 'Unclassified' AND [BusinesSectorDescription] <> 'Unknown');
OPEN businessCursor
FETCH NEXT FROM businessCursor INTO @businessId, @description, @industryId, @childDescription

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @parentId = (SELECT [Id] FROM [MarketData].[EconomicSectorsStructure] WHERE [SectorId] = @businessId AND [Description] = @description)
	IF @parentId is null
	BEGIN
		INSERT INTO [MarketData].[EconomicSectorsStructure]([SectorId], [Description]) 
		VALUES (@businessId, @description);
		SET @parentId = SCOPE_IDENTITY()
	END

	UPDATE [MarketData].[EconomicSectorsStructure] 
	SET [ParentId] = @parentId
	WHERE [SectorId] = @industryId AND [Description] = @childDescription AND [Id] <> @parentId AND [Description] <> 'Unclassified'

	FETCH NEXT FROM businessCursor INTO @businessId, @description, @industryId, @childDescription
END

CLOSE businessCursor
DEALLOCATE businessCursor

-------------------------------------------------------------------
------------- Reads level 1 nodes (economics sectors) -------------
-------------------------------------------------------------------

DECLARE @grandChildDescription VARCHAR(100)

DECLARE sectorCursor CURSOR FOR
SELECT [EconomicSectorId], [EconomicSectorDescription], [BusinesSectorId], [BusinesSectorDescription], [IndustryGroupId], [IndustryGroupDescription]
FROM [MarketData].[EconomicSector] WHERE [EconomicSectorId] <> 0 OR ([EconomicSectorDescription] <> '' AND [EconomicSectorDescription] <> 'Unclassified' AND [EconomicSectorDescription] <> 'Unknown');;
OPEN sectorCursor
FETCH NEXT FROM sectorCursor INTO @economicsId, @description, @businessId, @childDescription, @industryId, @grandChildDescription

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @parentId = (SELECT [Id] FROM [MarketData].[EconomicSectorsStructure] WHERE [SectorId] = @economicsId AND [Description] = @description)
	IF @parentId is null
	BEGIN
		INSERT INTO [MarketData].[EconomicSectorsStructure]([SectorId], [Description])
		VALUES (@economicsId, @description);
		SET @parentId = SCOPE_IDENTITY()
	END

	UPDATE [MarketData].[EconomicSectorsStructure] 
	SET [ParentId] = @parentId
	WHERE [SectorId] = @businessId AND [Description] = @childDescription AND [Id] <> @parentId AND [Description] <> 'Unclassified'

	
	-- also update second level nodes (fix for GLG Prod)
	IF @businessId = 0 AND (@childDescription = '' OR @childDescription = 'Unclassified' OR @childDescription = 'Unknown') AND 
		(@industryId <> 0 OR (@grandChildDescription <> '' AND @grandChildDescription <> 'Unclassified' AND @grandChildDescription <> 'Unknown'))
	BEGIN
		UPDATE [MarketData].[EconomicSectorsStructure] 
		SET [ParentId] = @parentId
		WHERE [SectorId] = @industryId AND [Description] = @grandChildDescription AND [Id] <> @parentId AND [Description] <> 'Unclassified'
	END

	FETCH NEXT FROM sectorCursor into @economicsId, @description, @businessId, @childDescription, @industryId, @grandChildDescription
END

CLOSE sectorCursor
DEALLOCATE sectorCursor

GO

ALTER TABLE [MarketData].[Security] DROP CONSTRAINT [FK_Security_EconomicSector]
GO

ALTER INDEX [SecurityViewIndex] ON [MarketData].[SecurityView]
DISABLE;
GO

IF OBJECT_ID ('MarketData.SecurityView', 'V') IS NOT NULL
DROP VIEW [MarketData].[SecurityView] ;
GO

EXEC sp_rename 'MarketData.EconomicSector', 'EconomicSectorDeprecated';
GO
EXEC sp_rename 'MarketData.EconomicSectorsStructure', 'EconomicSector';
GO

DROP TABLE [MarketData].[EconomicSectorDeprecated];
GO

--FK_Security_EconomicSector
ALTER TABLE [MarketData].[Security]  WITH NOCHECK ADD  CONSTRAINT [FK_Security_EconomicSector] FOREIGN KEY([EconomicSectorId])
REFERENCES [MarketData].[EconomicSector] ([Id])
GO

ALTER TABLE [MarketData].[Security] CHECK CONSTRAINT [FK_Security_EconomicSector]
GO
PRINT N'FK_Security_EconomicSector'
GO

-- recreate views

SET ANSI_NULLS ON
SET ANSI_PADDING ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [MarketData].[SecurityView]
WITH SCHEMABINDING 
AS
SELECT        Extent1.Id, Extent1.Name, Extent1.Ric, Extent1.LongName, Extent1.ParentId, Extent1.Isin, Extent1.Ticker, Extent2.Name AS Class, Extent3.Description AS Sector, 
                         Extent4.CountryDescription AS Country, Extent5.Name AS Currency
FROM            MarketData.[Security] AS Extent1 INNER JOIN
                         MarketData.SecurityClass AS Extent2 ON Extent1.ClassId = Extent2.Id INNER JOIN
                         MarketData.EconomicSector AS Extent3 ON Extent1.EconomicSectorId = Extent3.Id INNER JOIN
                         MarketData.Country AS Extent4 ON Extent1.CountryId = Extent4.Id INNER JOIN
                         MarketData.Currency AS Extent5 ON Extent1.CurrencyId = Extent5.Id
GO
PRINT N'CREATE View [MarketData].[SecurityView] FINISH'

-- Enable Index

CREATE UNIQUE CLUSTERED INDEX [SecurityViewIndex]
    ON [MarketData].[SecurityView]([Id] ASC);
GO

CREATE FULLTEXT INDEX ON [MarketData].[SecurityView]
    ([Name] LANGUAGE 1033, [Ric] LANGUAGE 1033, [LongName] LANGUAGE 1033, [Isin] LANGUAGE 1033, 
	[Class] LANGUAGE 1033, [Sector] LANGUAGE 1033, [Country] LANGUAGE 1033, [Currency] LANGUAGE 1033,
	[Ticker] LANGUAGE 1033)
    KEY INDEX [SecurityViewIndex]
	ON [IIP]
	WITH STOPLIST = OFF;
GO

PRINT N'CREATE Index [SecurityViewIndex] FINISH'
